package com.power.dao.impl;

import com.power.common.Contants;
import com.power.dao.DefectAdDao;
import com.power.ov.ResultData;
import com.power.ov.ResultDataTable;
import com.power.pojo.Defecttype;
import com.power.pojo.Sovle;
import com.power.utils.JdbcBuild;
import sun.security.pkcs11.wrapper.Constants;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created/**
 * Created with IntelliJ IDEA.
 * User: lh
 * Date: 2020/10/03 14:46
 * Description:
 * Version: V1.0
 */

public class DefectAdDaoImpl extends JdbcBuild implements DefectAdDao {
    @Override
    public ResultDataTable queryEmployeeList(int currentPage, int pageSize) throws SQLException {
        String sql = "select de.defect_id,de.defect_name,st.state_name from defecttype as de,states as st where de.defect_state=st.state_id and defect_state=?  limit ?,?";
        int param1 = (currentPage - 1)*pageSize;
        int param2 = pageSize;
        Object[] params = {Contants.AV_STATUS,param1,param2};
        System.out.println(params);
        List<Map<String,Object>> defectList = super.findAllForList(sql,params);
        ResultDataTable resultDataTable = new ResultDataTable();
        resultDataTable.setCode(0);
        resultDataTable.setCount(totalEmployeeSize());
        resultDataTable.setData(defectList);
        return resultDataTable;
    }

    @Override
    public ResultDataTable defectGrade(int currentPage, int pageSize) throws SQLException {
        String sql = "SELECT\n" +
                "distinct \n" +
                "\tins.task_id,\n" +
                "\tde.defect_details_id,\n" +
                "\tde.line_id,\n" +
                "\tde.tower_id,\n" +
                "\tdety.defect_name,\n" +
                "\tde.intact,\n" +
                "\tde.defect_des,\n" +
                "\tde.find_time,\n" +
                "\tus.user_name\n" +
                "\t\n" +
                "FROM\n" +
                "\tinspection AS ins,\n" +
                "\tdefect_grade AS grade,\n" +
                "\tdefecttype AS dety,\n" +
                "\tdefect_details AS de,\n" +
                "\tusers AS us\n" +
                "WHERE\n" +
                "\tins.inspection_account = de.inspector_account\n" +
                "AND de.defect_id = dety.defect_id\n" +
                "AND us.user_account = de.inspector_account\n" +
                "AND\n" +
                "ins.line_id=de.line_id\n" +
                "AND\n" +
                "ins.defect_grade is null limit ?,?";
        int param1 = (currentPage - 1)*pageSize;
        int param2 = pageSize;
        Object[] params = {param1,param2};
        System.out.println(params.toString());
        List<Map<String,Object>> defectList = super.findAllForList(sql,params);
        ResultDataTable resultDataTable = new ResultDataTable();
        resultDataTable.setCode(0);
        resultDataTable.setCount(totaldefectGrade());
        resultDataTable.setData(defectList);
        return resultDataTable;
    }
    private long totaldefectGrade() throws SQLException {
        String sql = "SELECT\n" +
                "\tcount(distinct ins.task_id)\n" +
                "\t\n" +
                "FROM\n" +
                "\tinspection AS ins,\n" +
                "\tdefect_grade AS grade,\n" +
                "\tdefecttype AS dety,\n" +
                "\tdefect_details AS de,\n" +
                "\tusers AS us\n" +
                "WHERE\n" +
                "\tins.inspection_account = de.inspector_account\n" +
                "AND de.defect_id = dety.defect_id\n" +
                "AND us.user_account = de.inspector_account\n" +
                "AND\n" +
                "ins.line_id=de.line_id\n" +
                "AND\n" +
                "ins.defect_grade is null";
        long count = super.findAllForInt(sql);
        return count;
    }
    @Override
    public int delDefectById(int newdefect_state,String defect_id) throws SQLException {
        String sql = "update  defecttype set defect_state=? where defect_id =?";
        Object[] params = {newdefect_state,defect_id};
        int result = super.executeUpdate(sql,params);
        return result;
    }



    @Override
    public int updateDefecttype(Defecttype defecttype) throws SQLException {
        String sql="update defecttype set defect_name=?,defect_state=? where defect_id =?";
        Object[] params={defecttype.getDefect_name(),defecttype.getDefect_state(),defecttype.getDefect_id()};
        int res=super.executeUpdate(sql,params);
        return res;
    }

    @Override
    public int addDefecttype(Defecttype defecttype) throws SQLException {
        String sql = "insert into defecttype(defect_name,defect_state) values(?,?)";
        Object[] params = {defecttype.getDefect_name(),defecttype.getDefect_state()};
        int result = super.executeUpdate(sql,params);
        return result;
    }

    @Override
    public int updateGrade(String defect_grade,String task_id) throws SQLException {
        String sql = "update inspection set defect_grade=? where task_id=?";
        String sq2 = "update defect_details set defect_grade=? where line_id=(SELECT line_id FROM inspection where inspection.task_id=?)";
        Object[] params = {defect_grade,task_id};
        int result = super.executeUpdate(sql,params);
        int res=0;
        if (result>0){
            res =super.executeUpdate(sq2,params);
        }
        return res;
    }

    @Override
    public ResultDataTable querySolveTask(int currentPage, int pageSize)throws SQLException {
        String sql = "SELECT ins.task_id,ins.task_name,wo.work_order_des,us.user_name,ins.lssue_time,states.state_name,ins.completion_time,yes.yesno_name FROM inspection AS ins,states AS states,yesno AS yes,work_order AS wo,users AS us WHERE ins.inspection_account=us.user_account AND wo.work_id=ins.work_id AND yes.yesno_id=ins.is_detele AND states.state_id=ins.task_state limit ?,?";
        int param1 = (currentPage - 1)*pageSize;
        int param2 = pageSize;
        Object[] params = {param1,param2};
        System.out.println(params.toString());
        List<Map<String,Object>> defectList = super.findAllForList(sql,params);
        ResultDataTable resultDataTable = new ResultDataTable();
        resultDataTable.setCode(0);
        resultDataTable.setCount(totalquerySolveTask());
        resultDataTable.setData(defectList);
        return resultDataTable;
    }

    @Override
    public int addSovle(Sovle sovle) throws SQLException {
        String sql = "insert into sovle(sovle_task,solve_name,work_id,user_id,lssue_time,sovle_dec,sovle_remarks,sovle_account,intact,defect_id,defect_details_id) values(?,?,?,?,?,?,?,?,?,?,?)";
        Object[] params = {sovle.getSovle_task(),sovle.getSolve_name(),sovle.getWork_id(),sovle.getUser_id(),sovle.getLssue_time(),sovle.getSovle_dec(),sovle.getSovle_remarks(),sovle.getSovle_account(),sovle.getIntact(),sovle.getDefect_id(),sovle.getDefect_details_id()};
        int result = super.executeUpdate(sql,params);
        return result;
    }

    @Override
    public int updateYesNo(String sovle_task, int is_delete) throws SQLException {
        String sql = "update sovle set is_delete=? where sovle_task=?";
        Object[] params = {is_delete,sovle_task};
        int result = super.executeUpdate(sql,params);
        System.out.println("dao"+sovle_task+" "+is_delete);
        System.out.println(result);
        return result;
    }

    @Override
    public ResultDataTable queryOption() throws SQLException {
        String sql = "SELECT * FROM users";
        List<Users> inspetions = super.queryListEntity(Users.class, sql, null);
        ResultDataTable resultDataTable = new ResultDataTable();
        resultDataTable.setData(inspetions);
        resultDataTable.setCode(0);
        resultDataTable.setMessage("查询成功");
        return resultDataTable;
    }

    @Override
    public int addSovleAccount(String sovle_account, String sovle_task) throws SQLException {
        String sql = "update sovle set sovle_account=? where sovle_task=?";
        Object[] params = {sovle_account,sovle_task};
        int result = super.executeUpdate(sql,params);
        return result;
    }

    private long totalEmployeeSize() throws SQLException {
        String sql = "select count(*) from defecttype  where defect_state=1";
        long count = super.findAllForInt(sql);
        return count;
    }
    private long totalquerySolveTask() throws SQLException {
        String sql = "select count(*) FROM inspection AS ins,states AS states,yesno AS yes," +
                "work_order AS wo,users AS us WHERE ins.inspection_account=us.user_account AND " +
                "wo.work_id=ins.work_id AND yes.yesno_id=ins.is_detele AND " +
                "states.state_id=ins.task_state";
        long count = super.findAllForInt(sql);
        return count;
    }
}
